package unittests;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import server.SQLConnection;
/**
 * 
 * @author Ron Shenkerman
 * @author Gabi Revah
 * @author Aviad Almog
 * @author Ivan Onyshchenko
 */
public class ReportTest {

	
	
	/**
	 * 
	 * @return number of active subscribers in the system
	 * @throws SQLException
	 */
	public int getnumofactive() throws SQLException{
		
		SQLConnection sc = new SQLConnection();
		PreparedStatement active = sc.getConnection().prepareStatement("SELECT COUNT(ID) FROM subscriber WHERE status=0;");
		sc.getConnection().setAutoCommit(true);
		
		java.sql.ResultSet rsactive =  active.executeQuery();
		rsactive.next();
		return rsactive.getInt(1);
		
	}
	/**
	 * 
	 * @return number of frozen subscribers in the system
	 * @throws SQLException
	 */
	public int getnumoffrozen() throws SQLException{
		
		SQLConnection sc = new SQLConnection();
		PreparedStatement freeze = sc.getConnection().prepareStatement("SELECT COUNT(ID) FROM subscriber WHERE status=2;");
		java.sql.ResultSet rsfreeze =  freeze.executeQuery();
		rsfreeze.next();
		return(rsfreeze.getInt(1));
	}
	/**
	 * 
	 * @return number of lock subscribers in the system
	 * @throws SQLException
	 */
	public int getnumoflock() throws SQLException{
		
		SQLConnection sc = new SQLConnection();
		PreparedStatement lock = sc.getConnection().prepareStatement("SELECT COUNT(ID) FROM subscriber WHERE status=1;");
		java.sql.ResultSet rslock =  lock.executeQuery();
		rslock.next();
		return(rslock.getInt(1));
	}
	/**
	 * 
	 * @return number book copies that where lended to subscribers in the system
	 * @throws SQLException
	 */
	public int getnumoflending() throws SQLException{
		
		SQLConnection sc = new SQLConnection();
		PreparedStatement lend = sc.getConnection().prepareStatement("SELECT COUNT(LendId) FROM db1.lending ;");
		java.sql.ResultSet rslend =  lend.executeQuery();
		rslend.next();
		return(rslend.getInt(1));
	}
	
	
	
	
	
	
}
